
/**
 * Copyright (2024, ) Institute of Software, Chinese Academy of Sciences
 * author: wuheng@iscas.ac.cn
 * since:  0.1.0
 **/

use diesel::prelude::*;
use diesel::{pg::PgConnection, sql_query, result::Error, deserialize};
use diesel::deserialize::FromSql;
use diesel::sql_types::{BigInt, Bool};
use diesel::QueryableByName;
use serde_json::Value;
use std::collections::HashMap;
use diesel::sql_types::Json;
use serde_json::json;
use diesel::sql_types::Text;
use diesel::sql_types::Timestamp;
use serde::{Serialize, Deserialize};
use diesel::SqliteConnection;
#[derive(QueryableByName, Serialize, Deserialize)]
struct ExistsResult {
    #[sql_type = "Bool"]
    exists: bool,
}

#[derive(QueryableByName, Queryable, Serialize, Deserialize)]
#[derive(Debug)]
struct CountResult {
    #[sql_type = "BigInt"]
    total_count: i64,
}

#[derive(QueryableByName, Queryable, Serialize, Deserialize)]
#[diesel(check_for_backend(diesel::pg::Pg))]
struct CommonPostgresObject {
    #[sql_type = "Text"]
    name: String,

    #[sql_type = "Text"]
    namespace: String,

    #[sql_type = "Text"]
    apigroup: String,

    #[sql_type = "Json"]
    data: Value,

    #[sql_type = "Timestamp"]
    created: chrono::NaiveDateTime,

    #[sql_type = "Timestamp"]
    updated: chrono::NaiveDateTime,
}


#[derive(QueryableByName, Queryable, Serialize, Deserialize)]
#[diesel(check_for_backend(diesel::sqlite::Sqlite))]
struct CommonSqlite3Object {
    #[sql_type = "Text"]
    name: String,

    #[sql_type = "Text"]
    namespace: String,

    #[sql_type = "Text"]
    apigroup: String,

    #[sql_type = "Text"]
    data: String,

    #[sql_type = "Timestamp"]
    created: chrono::NaiveDateTime,

    #[sql_type = "Timestamp"]
    updated: chrono::NaiveDateTime,
}

pub fn list_sqlite3_object(conn: &mut SqliteConnection, table: &str, conditions: HashMap<&str, &str>, page: i64, number: i64) -> Result<Value, Error> {
    let number = if number > 0 { number } else { 10 };
    let offset = (page - 1) * number;

    // 构造查询条件
    let mut query_conditions = String::new();
    for (field, value) in &conditions {
        query_conditions.push_str(&format!("{} = '{}' AND ", field, value));
    }
    if !query_conditions.is_empty() {
        query_conditions.pop(); // 移除最后一个空格
        query_conditions.pop(); // 移除最后一个 "AND"
    }

    // 构造 SQL 查询语句
    let base_query = format!(
        "SELECT name, namespace, apigroup, data, created, updated FROM {} {} LIMIT {} OFFSET {}",
        table,
        if query_conditions.is_empty() { "".to_string() } else { format!("WHERE {}", query_conditions) },
        number,
        offset
    );

    let count_query = format!(
        "SELECT COUNT(*) AS total_count FROM {} {}",
        table,
        if query_conditions.is_empty() { "".to_string() } else { format!("WHERE {}", query_conditions) }
    );

    let result: CountResult = sql_query(count_query).get_result(conn)?;
    // let total_count: i64 = result.total_count;

    println!{"{:?}", result}
    let total_count = result.total_count;

    // 查询当前页的数据
    let results: Vec<CommonSqlite3Object> = sql_query(base_query).load::<CommonSqlite3Object>(conn)?;

    // 构造JSON结果
    let json_results: Vec<Value> = results.into_iter().map(|(obj)| {
        json!({
            "name": obj.name,
            "namespace": obj.namespace,
            "apigroup": obj.apigroup,
            "data": obj.data,
            "created": obj.created.format("%Y-%m-%d %H:%M:%S").to_string(),
            "updated": obj.updated.format("%Y-%m-%d %H:%M:%S").to_string(),
        })
    }).collect();

    // 构造分页结果的JSON响应
    let response = json!({
        "total_count": total_count,
        "current_page": page,
        "per_page": number,
        "total_pages": (total_count as f64 / number as f64).ceil() as i64,
        "results": json_results
    });

    Ok(response)
}

pub fn list_postgres_object(conn: &mut PgConnection, table: &str, conditions: HashMap<&str, &str>, page: i64, number: i64) -> Result<Value, Error> {
    let number = if number > 0 { number } else { 10 };
    let offset = (page - 1) * number;

    // 构造查询条件
    let mut query_conditions = String::new();
    for (field, value) in &conditions {
        query_conditions.push_str(&format!("{} = '{}' AND ", field, value));
    }
    if !query_conditions.is_empty() {
        query_conditions.pop(); // 移除最后一个空格
        query_conditions.pop(); // 移除最后一个 "AND"
    }

    // 构造 SQL 查询语句
    let base_query = format!(
        "SELECT name, namespace, apigroup, data, created, updated FROM {} {} LIMIT {} OFFSET {}",
        table,
        if query_conditions.is_empty() { "".to_string() } else { format!("WHERE {}", query_conditions) },
        number,
        offset
    );

    let count_query = format!(
        "SELECT COUNT(*) AS total_count FROM {} {}",
        table,
        if query_conditions.is_empty() { "".to_string() } else { format!("WHERE {}", query_conditions) }
    );

    let result: CountResult = sql_query(count_query).get_result(conn)?;
    // let total_count: i64 = result.total_count;

    println!{"{:?}", result}
    let total_count = result.total_count;

    // 查询当前页的数据
    let results: Vec<CommonPostgresObject> = sql_query(base_query).load::<CommonPostgresObject>(conn)?;

    // 构造JSON结果
    let json_results: Vec<Value> = results.into_iter().map(|(obj)| {
        json!({
            "name": obj.name,
            "namespace": obj.namespace,
            "apigroup": obj.apigroup,
            "data": obj.data,
            "created": obj.created.format("%Y-%m-%d %H:%M:%S").to_string(),
            "updated": obj.updated.format("%Y-%m-%d %H:%M:%S").to_string(),
        })
    }).collect();

    // 构造分页结果的JSON响应
    let response = json!({
        "total_count": total_count,
        "current_page": page,
        "per_page": number,
        "total_pages": (total_count as f64 / number as f64).ceil() as i64,
        "results": json_results
    });

    Ok(response)
}

fn generate_postgres_list_objects_sql(table_name: &str) -> String {
    format!("SELECT * FROM {};",
            table_name
    )
}

pub fn query_postgres_table(conn: &mut PgConnection, table: &str) -> Result<bool, diesel::result::Error> {
    let result: ExistsResult = sql_query(generate_postgres_query_table_sql(table)).get_result(conn)?;
    Ok(result.exists)
}


fn generate_postgres_query_table_sql(table_name: &str) -> String {
    format!("SELECT EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = 'public' AND table_name = '{}'
        ) AS exists;",
            table_name
    )
}

pub fn query_sqlite3_table(conn: &mut SqliteConnection, table: &str) -> Result<bool, diesel::result::Error> {
    match sql_query(generate_sqlite3_query_table_sql(table)).execute(conn) {
        Ok(_) => Ok(true),
        Err(_) => Ok(false),
    }
}

fn generate_sqlite3_query_table_sql(table_name: &str) -> String {
    format!("SELECT 1 FROM {} LIMIT 1;",
            table_name
    )
}